/**
 * 
 */
package com.disys.mb;

import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.Serializable;
import java.math.BigInteger;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.activation.MimetypesFileTypeMap;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;

import com.disys.common.IRecordType;
import com.disys.common.Util;
import com.disys.common.WriteToFile;
import com.disys.database.connectivity.DBConnectionManager;
import com.disys.facade.ProcessFacade;
import com.disys.facade.Rec140ERWOTCLOCFacade;
import com.disys.facade.support.Rec140ERWOTCLOCSupportFacade;
import com.disys.model.Process;
import com.disys.model.Rec140ERWOTCLOC;
import com.disys.model.support.Rec140ERWOTCLOCSupport;

/**
 * @author Ubaid Raja
 * 
 */
@ViewScoped
@ManagedBean(name = "rec140MB")
public class Rec140ERWOTCLOCMB extends AbstractMB implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = -7932940729870613187L;
	public static final String INJECTION_NAME = "#{rec140MB}";
	private static final transient Logger logger = Logger
			.getLogger(Rec140ERWOTCLOCMB.class.getName());

	private Rec140ERWOTCLOC record;

	private List<Rec140ERWOTCLOC> records;

	private Rec140ERWOTCLOCFacade recFacade;

	private Rec140ERWOTCLOCSupportFacade preFacade;

	private ProcessFacade objProcessFacade = new ProcessFacade();

	public Statement selectStatement;
	public ResultSet selectRs;
	public Statement s2;
	public ResultSet rs2;
	public PreparedStatement psmtMySQL;
	private Map<Integer,Integer> processList;
	private Integer selectedProcessId;
	
	public Rec140ERWOTCLOCMB(){
		System.out.println("Rec140ERWOTCLOCMB Called");
	}
	
	public void importRecords() {
		logger.info("Trying to import records");
		System.out.println("Trying to import records");
		doMigration();
	}

	public String getSelectedProcessRecords(){
		if(selectedProcessId==-1){
			displayErrorMessageToUser("Select a process first");
			return "";
		}
		BigInteger selPId = Util.getBigInteger(selectedProcessId);
		System.out.println("BigInteger selPId "+selPId);
		List<Rec140ERWOTCLOC> list = getRecFacade().findSelectedProcessRecords(IRecordType.REC140, selPId);
		this.setRecords(list);
		displayInfoMessageToUser("Data loaded successfully");
		return "";
	}
	// public void doMigration() {
	//
	// Connection con;
	// con = DBConnectionManager.getConnection(DBConnectionManager.SQLSERVER);
	// //con2 = DBConnectionManager.getConnection(DBConnectionManager.MYSQL);
	// String sql = "SELECT * FROM PS_LOCATION_TBL;";
	// if (con != null) {
	// try {
	// selectStatement = con.createStatement();
	//
	// selectRs = selectStatement.executeQuery(sql);
	// Rec140ERWOTCLOC obj;
	//
	// // sql = "INSERT INTO tx_140ERWOTCLOC (FIELD1, FIELD2, FIELD3) "
	// // + "VALUES (?, ?, ?);";
	//
	// //con2.setAutoCommit(false);
	// //psmtMySQL = con2.prepareStatement(sql);
	// //con2.setAutoCommit(true);
	// Rec140ERWOTCLOCSupport predefinedValues = new Rec140ERWOTCLOCSupport();
	// predefinedValues = getPreFacade().findSingleRecord();
	// while (selectRs.next()) {
	// obj = new Rec140ERWOTCLOC();
	// try {
	// //read data from the support table
	// obj.setCocode(predefinedValues.getCocode());
	// obj.setRectype(predefinedValues.getRectype());
	// obj.setTci_id(predefinedValues.getTci_id());
	// obj.setWotcfacilitybegindt(predefinedValues.getWotcfacilitybegindt());
	//
	// //obj.setFiller(selectRs.getString("filler"));
	// //obj.setRectype(selectRs.getString("rectype"));
	// obj.setWotclocaddr_1(selectRs.getString("ADDRESS1"));
	// obj.setWotclocaddr_2(selectRs.getString("ADDRESS2"));
	// obj.setWotclocname(selectRs.getString("DESCR"));
	// obj.setWotcloccity_1(selectRs.getString("CITY"));
	// obj.setWotclocstate_1(selectRs.getString("STATE"));
	// obj.setWotcloczip_1(selectRs.getString("POSTAL"));
	// String country = selectRs.getString("COUNTRY");
	// if(country.length() > 2){
	// country = country.substring(0,2);
	// }
	// obj.setWotcloccountry_1(country);
	//
	// obj.setFinalized(false);
	// recFacade.insertRecord(obj);
	// } catch (SQLException sqle) {
	// sqle.printStackTrace();
	// }
	// }
	// //psmtMySQL.executeBatch();
	// //con2.setAutoCommit(true);
	// //psmtMySQL.close();
	// //rs2.close();
	// //s2.close();
	// //con2.close();
	// selectRs.close();
	// selectStatement.close();
	// con.close();
	// } catch (SQLException e) {
	// e.printStackTrace();
	// }
	// }
	// }

	public String doMigration() {

		// String format = "yyyyMMdd";

		// SimpleDateFormat dateFormat = new SimpleDateFormat(format);
		Date date = new Date();

		Calendar cal = Calendar.getInstance();
		// date = dateFormat.format(date);
		cal.setTime(date);
		int week = cal.get(Calendar.WEEK_OF_YEAR);
		System.out.println("Current week number is : " + week);

		Connection con, con2;
		con = DBConnectionManager.getConnection(DBConnectionManager.SQLSERVER);
		con2 = DBConnectionManager.getConnection(DBConnectionManager.MYSQL);
		String sql = "SELECT * FROM PS_LOCATION_TBL;";
		String mysql = "";
		String deleteSql = "";
		Integer deletedRecords = 0;
		Integer addedRecords = 0;
		if (con != null && con2 != null) {
			try {

				HashMap<String, Integer> sizeOfColumns = new HashMap<String, Integer>();
				sizeOfColumns = getMetaData(con2);
				selectStatement = con.createStatement();
				selectRs = selectStatement.executeQuery(sql);

				deleteSql = "delete from " + con2.getCatalog()
						+ ".tx_140erwotcloc where finalized = false";
				s2 = con2.createStatement();
				deletedRecords = s2.executeUpdate(deleteSql);

				s2.close();
				mysql = "INSERT INTO "
						+ con2.getCatalog()
						+ ".tx_140erwotcloc (cocode,rectype,tci_id,wotcfacilitybegindt, wotclocaddr_1, wotclocaddr_2, wotclocname,wotcloccity_1,finalized, process_id) "
						+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

				con2.setAutoCommit(false);
				psmtMySQL = con2.prepareStatement(mysql);
				con2.setAutoCommit(true);
				Integer length = 0;
				Process objProcess = new Process();
				//objProcess.setBatch(1);
				//objProcess.setEnding_date(new Date());
				//TODO get the id of the user that executed this command
				//objProcess.setExecutedBy(executedBy);
				
				objProcess.setExecutionTime(new Timestamp(new Date().getTime()));
				objProcess.setLastFileExportDate(new Date());
				objProcess.setStatus("Draft");
				//objProcess.setExecution_type("tx_140erwotcloc");
//				objProcess.setSent(false);
				objProcess.setWeek(week);

				Integer processId = objProcessFacade
						.insertProcessRecord(objProcess);

				Rec140ERWOTCLOCSupport predefinedValues = new Rec140ERWOTCLOCSupport();
				predefinedValues = getPreFacade().findSingleRecord();
				while (selectRs.next()) {
					addedRecords = addedRecords + 1;
					try {
						// read data from the support table
						psmtMySQL.setString(1, predefinedValues.getCocode());
						psmtMySQL.setString(2, predefinedValues.getRectype());
						psmtMySQL.setString(3, predefinedValues.getTci_id());
						psmtMySQL.setString(4,
								predefinedValues.getWotcfacilitybegindt());

						String wotclocaddr_1 = selectRs.getString("ADDRESS1");
						length = sizeOfColumns.get("wotclocaddr_1");
						if (wotclocaddr_1.length() > length) {
							wotclocaddr_1 = wotclocaddr_1.substring(0, length);
						}
						psmtMySQL.setString(5, wotclocaddr_1);

						String wotclocaddr_2 = selectRs.getString("ADDRESS2");
						length = sizeOfColumns.get("wotclocaddr_2");
						if (wotclocaddr_2.length() > length) {
							wotclocaddr_2 = wotclocaddr_2.substring(0, length);
						}
						psmtMySQL.setString(6, wotclocaddr_2);

						String wotclocname = selectRs.getString("DESCR");
						length = sizeOfColumns.get("wotclocname");
						if (wotclocname.length() > length) {
							wotclocname = wotclocname.substring(0, length);
						}

						psmtMySQL.setString(7, wotclocname);

						String wotcloccity_1 = selectRs.getString("CITY");
						length = sizeOfColumns.get("wotcloccity_1");
						if (wotcloccity_1.length() > length) {
							wotcloccity_1 = wotcloccity_1.substring(0, length);
						}

						psmtMySQL.setString(8, wotcloccity_1);

						psmtMySQL.setBoolean(9, false);

						psmtMySQL.setInt(10, processId);

						psmtMySQL.addBatch();
					} catch (SQLException sqle) {
						sqle.printStackTrace();
					}
				}
				psmtMySQL.executeBatch();
				con2.setAutoCommit(true);
				psmtMySQL.close();
				// rs2.close();
				// s2.close();
				con2.close();
				selectRs.close();
				selectStatement.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		displayInfoMessageToUser(deletedRecords + " records delete and "
				+ addedRecords + " records added successfully!!!");
		return "";
	}

	public HashMap<String, Integer> getMetaData(Connection connection) {
		Connection con;
		con = DBConnectionManager.getConnection(DBConnectionManager.MYSQL);
		HashMap<String, Integer> sizeOfColumns = new HashMap<String, Integer>();
		if (con != null) {
			try {
				ResultSet rsColumns = null;
				DatabaseMetaData meta = con.getMetaData();
				rsColumns = meta
						.getColumns(null, null, "tx_140ERWOTCLOC", null);
				while (rsColumns.next()) {
					String columnName = rsColumns.getString("COLUMN_NAME");
					int size = rsColumns.getInt("COLUMN_SIZE");
					sizeOfColumns.put(columnName, size);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return sizeOfColumns;
	}

	public String exportRecords() {
		// FixedFormatManager manager = new FixedFormatManagerImpl();
		Connection con;
		con = DBConnectionManager.getConnection(DBConnectionManager.MYSQL);

		HashMap<String, Integer> sizeOfColumns = getMetaData(con);

		//TODO get the latest process and find the result on its basis
		List<Rec140ERWOTCLOC> objRec140erwotclocs = getRecFacade()
				.findFinalizeRecords("Rec140ERWOTCLOC", 0,Util.getBigInteger(1));
		WriteToFile objWriteToFile = new WriteToFile();
		StringBuffer contents = new StringBuffer();
		String filePath = "C:/Rec140ERWOTCLOC.txt";
		String recordSeperator = "\r \n";
		System.out.println("Size of the list is : "
				+ objRec140erwotclocs.size());
		Integer length = 0;
		File file = objWriteToFile.createFile(filePath);
		for (Rec140ERWOTCLOC ob : objRec140erwotclocs) {
			// Rec140ERWOTCLOC record = manager.load(Rec140ERWOTCLOC.class, ob);
			// System.out.println(ob.getCocode());
			// System.out.println(ob.getId());

			// System.out.println("Exported: " + manager.export(record));

			contents.append(ob.getCocode());
			contents.append(ob.getRectype());
			contents.append(ob.getTci_id());
			contents.append(ob.getWotcfacilitybegindt());

			String wotclocaddr_1 = ob.getWotclocaddr_1();
			length = sizeOfColumns.get("wotclocaddr_1");
			if (wotclocaddr_1.length() < length) {
				wotclocaddr_1 = appendSpaces(wotclocaddr_1, length);
			}
			contents.append(wotclocaddr_1);

			String wotclocaddr_2 = ob.getWotclocaddr_2();
			length = sizeOfColumns.get("wotclocaddr_2");
			if (wotclocaddr_2.length() < length) {
				wotclocaddr_2 = appendSpaces(wotclocaddr_2, length);
			}
			contents.append(wotclocaddr_2);

			String wotclocname = ob.getWotclocname();
			length = sizeOfColumns.get("wotclocname");
			if (wotclocname.length() < length) {
				wotclocname = appendSpaces(wotclocname, length);
			}
			contents.append(wotclocname);

			String wotcloccity_1 = ob.getWotcloccity_1();
			length = sizeOfColumns.get("wotcloccity_1");
			if (wotcloccity_1.length() < length) {
				wotcloccity_1 = appendSpaces(wotcloccity_1, length);
			}
			contents.append(wotcloccity_1);

			contents.append(recordSeperator);
			objWriteToFile.writeToFile(file, contents.toString());
		}

		// objWriteToFile.writeToFile(filePath, contents.toString());

		
		displayInfoMessageToUser(objRec140erwotclocs.size()
				+ " records exported successfully!!!");
		return "";
	}

	public void download(){
		HttpServletRequest requestObj = (HttpServletRequest) FacesContext
				.getCurrentInstance().getExternalContext().getRequest();

		HttpServletResponse responseObj = (HttpServletResponse) FacesContext
				.getCurrentInstance().getExternalContext().getResponse();
		String filePath = "C:/Rec140ERWOTCLOC.txt";
		outputFile(requestObj,responseObj,filePath);
	}
	
	public void outputFile(HttpServletRequest requestObj,
			HttpServletResponse response, String filePath) {
		File file = new File(filePath);
		int length = 0;
		ServletOutputStream outStream = null;
		try {
			response.resetBuffer();
			try{
				outStream = response.getOutputStream();
			}catch(IllegalStateException e){
				System.err.println(e.getMessage());
			}
			response.setContentType(new MimetypesFileTypeMap()
					.getContentType(file));
			response.setContentLength((int) file.length());
			response.setHeader("content-disposition", "attachment; filename="
					+ URLEncoder.encode(filePath, "UTF-8"));

			Integer BUFSIZE = 1024;
			byte[] byteBuffer = new byte[BUFSIZE];
			DataInputStream in = new DataInputStream(new FileInputStream(file));

			// reads the file's bytes and writes them to the response stream
			while ((in != null) && ((length = in.read(byteBuffer)) != -1)) {
				outStream.write(byteBuffer, 0, length);
			}

			in.close();
			outStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private String appendSpaces(String data, Integer requiredLength) {
		StringBuffer newString = new StringBuffer(data);
		for (int i = data.length(); i <= requiredLength; i++) {
			newString.append(' ');
		}
		return newString.toString();
	}

	/**
	 * @return the record
	 */
	public Rec140ERWOTCLOC getRecord() {
		return record;
	}

	/**
	 * @param record
	 *            the record to set
	 */
	public void setRecord(Rec140ERWOTCLOC record) {
		this.record = record;
	}

	/**
	 * @return the records
	 */
	public List<Rec140ERWOTCLOC> getRecords() {
		if (records == null) {
			allRecords();
		}
		return records;
	}
	
	public List<Rec140ERWOTCLOC> allRecords(){
		records = new ArrayList<Rec140ERWOTCLOC>();
		records = getRecFacade().findAllRecords();
		return records;
	}

	/**
	 * @param records
	 *            the records to set
	 */
	public void setRecords(List<Rec140ERWOTCLOC> records) {
		this.records = records;
	}

	/**
	 * @return the recFacade
	 */
	public Rec140ERWOTCLOCFacade getRecFacade() {
		if (recFacade == null) {
			recFacade = new Rec140ERWOTCLOCFacade();
		}
		return recFacade;
	}

	/**
	 * @param recFacade
	 *            the recFacade to set
	 */
	public void setRecFacade(Rec140ERWOTCLOCFacade recFacade) {
		this.recFacade = recFacade;
	}

	/**
	 * @return the preFacade
	 */
	public Rec140ERWOTCLOCSupportFacade getPreFacade() {
		if (preFacade == null) {
			preFacade = new Rec140ERWOTCLOCSupportFacade();
		}
		return preFacade;
	}

	/**
	 * @param preFacade
	 *            the preFacade to set
	 */
	public void setPreFacade(Rec140ERWOTCLOCSupportFacade preFacade) {
		this.preFacade = preFacade;
	}

	/**
	 * @return the processList
	 */
	public Map<Integer, Integer> getProcessList() {
		if(processList == null){
			processList = new LinkedHashMap<Integer, Integer>();
			List<Integer> list = getRecFacade().getAllProcessIds(IRecordType.PROCESS);
			for(int i=0; i<list.size();i++){
				processList.put(list.get(i), list.get(i));
			}
		}
		return processList;
	}

	/**
	 * @param processList the processList to set
	 */
	public void setProcessList(Map<Integer, Integer> processList) {
		this.processList = processList;
	}

	/**
	 * @return the selectedProcessId
	 */
	public Integer getSelectedProcessId() {
		return selectedProcessId;
	}

	/**
	 * @param selectedProcessId the selectedProcessId to set
	 */
	public void setSelectedProcessId(Integer selectedProcessId) {
		this.selectedProcessId = selectedProcessId;
	}
}
